﻿#pragma once
#include <iostream>
#include <vector>
#include <time.h>
#include <string>
#include "mysql/jdbc.h"
#include "mysql_def.h"
#include "mysql_database.h"


class MySqlAPI
{
public:
	MySqlAPI();
	~MySqlAPI();

	int connect(const std::string& hostName, const std::string& userName,
		const std::string& password, const bool autocommit = false);
	int selectDataBase(const std::string& database);
	int selectTable(const std::string& tablename);
	int getTableRowCount(int& rowcount);
	int setSavePoint(std::string savepointname);
	int rollBackToSavePoint(sql::Savepoint*);
	int rollBackToSavePoint(std::string savepointname);
	int commit();
	int sortTableById(const std::string& tablename,bool force_commit = false);

	template<class T>
	int readWholeTable(std::vector<T>& data);

	template<class T>
	int readLinesInTable(const int startline, const int linecount, std::vector<T>& data);

	template<class T>
	int insertLinesInTable(std::vector<T> data);

	template<class T>
	int updateTable(T data);

	int deleteTableRows(std::vector<int> ids);

	bool isAutoCommitted() { return _autocommit; }
	std::string get_current_database_name() { return _current_databasename; }
	std::string get_current_table_name() { return _current_tablename; }
	std::vector <MysqlDataType> get_current_table_col_type() { return _current_table_col_type; }
	std::vector <std::string> get_current_table_col_name() { return _current_table_col_name; }
	std::vector<sql::Savepoint*> getSavePoints() { return savepoint_list; }
	int getSavePoint(std::string savepointname) {
		std::vector<sql::Savepoint*>::iterator target_iter = std::find_if(savepoint_list.begin(), savepoint_list.end(),
			[savepointname](sql::Savepoint* a) {
			std::string name = a->getSavepointName().c_str();
			return std::equal(name.begin(), name.end(), savepointname.begin(), savepointname.end()); });
		return (target_iter == savepoint_list.end()) ? -1 : target_iter - savepoint_list.begin();
	}

private:
	sql::mysql::MySQL_Driver* driver = nullptr;
	sql::Connection* conn = nullptr;
	sql::Statement *stmt = nullptr;
	std::vector<sql::Savepoint*>savepoint_list;
	
	bool _autocommit = false;
	bool _iscommitted = true;
	std::string _current_databasename,_current_tablename;
	std::vector <MysqlDataType> _current_table_col_type;
	std::vector <std::string> _current_table_col_name;
	
	mydate string_to_date(std::string str);//YYYY-MM-DD
	mytime string_to_time(std::string str);//HH:MM:SS
	mytime_ms string_to_time_ms(std::string str);//HH:MM:SS.MS
	mydatetime string_to_datetime(std::string str);//YYYY-MM-DD HH:MM:SS
	mydatetime_ms string_to_datetime_ms(std::string str);//YYYY-MM-DD HH:MM:SS.MS
	
	int getCurrentTablePropertys();
	int releaseAllSavePoints();

	template <typename T>
	void paser_ResultSet(sql::ResultSet *res, std::vector<T>& data);

};

template <typename T>
void MySqlAPI::paser_ResultSet(sql::ResultSet *res, std::vector<T>& data)
{
	infoprintf("read data size = %d\n", (int)res->rowsCount());
	while (res->next())
	{
		T datai = T();
		std::string str;
		for (int i = 0; i < datai._type.size(); i++)
		{
			switch (datai._type[i])
			{
			case TINYINT:
				*(char*)datai._value[i] = (char)res->getInt(i + 1);
				break;
			case SMALLINT:
				*(short*)datai._value[i] = (short)res->getInt(i + 1);
				break;
			case MEDIUMINT:
			case INT:
				*(int*)datai._value[i] = res->getInt(i + 1);
				break;
			case BIGINT:
				*(long*)datai._value[i] = res->getInt64(i + 1);
				break;
			case FLOAT:
			case DOUBLE:
			case DECIMAL:
				*(double*)datai._value[i] = res->getDouble(i + 1);
				break;
			case DATE:
				*(mydate*)datai._value[i] = string_to_date(res->getString(i + 1).c_str());
				break;
			case TIME:
				*(mytime*)datai._value[i] = string_to_time(res->getString(i + 1).c_str());
				break;
			case TIME_MS:
				*(mytime_ms*)datai._value[i] = string_to_time_ms(res->getString(i + 1).c_str());
				break;
			case YEAR:
				*(int*)datai._value[i] = std::stoi(std::string(res->getString(i + 1).c_str()));
				break;
			case DATETIME:
			case TIMESTAMP:
				*(mydatetime*)datai._value[i] = string_to_datetime(res->getString(i + 1).c_str());
				break;
			case DATETIME_MS:
				*(mydatetime_ms*)datai._value[i] = string_to_datetime_ms(res->getString(i + 1).c_str());
				break;
			case CHAR:
			case VARCHAR:
			case TINYBLOB:
			case TINYTEXT:
			case BLOB:
			case TEXT:
			case MEDIUMBLOB:
			case MEDIUMTEXT:
			case LONGBLOB:
			case LONGTEXT:
				str = res->getString(i + 1).c_str();
				*((std::string*)datai._value[i]) = str;
				break;
			default:
				break;
			}
		}
		data.push_back(datai);
		datai.printdata();
	}
}

template <typename T>
int MySqlAPI::readWholeTable(std::vector<T>& data)
{
	infoprintf( "readWholeTable:...\n");
	data.clear();
	sql::ResultSet *res = nullptr;
	try
	{
		std::string query = "select * from " + _current_tablename;
		res = stmt->executeQuery(query.c_str());// executeQuery适用于 SELECT
		paser_ResultSet(res, data);
	}
	catch (sql::SQLException& ex)
	{
		delete res;
		errorprintf(" readWholeTable failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		delete res;
		errorprintf(" readWholeTable failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}

	delete res;
	res = nullptr;
	return 0;
}

template<class T>
int MySqlAPI::readLinesInTable(const int startline, const int linecount, std::vector<T>& data)
{
	infoprintf( "readLinesInTable:...\n");
	data.clear();
	sql::ResultSet *res = nullptr;
	try
	{
		std::string query =
			"select * from " + _current_tablename + " limit " +
			std::to_string(startline - 1) + "," + std::to_string(linecount);
		res = stmt->executeQuery(query.c_str());// executeQuery适用于 SELECT
		paser_ResultSet(res, data);
	}
	catch (sql::SQLException& ex)
	{
		delete res;
		errorprintf(" readLinesInTable failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		delete res;
		errorprintf(" readLinesInTable failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}

	delete res;
	res = nullptr;
	return 0;
}

template<class T>
int MySqlAPI::insertLinesInTable(std::vector<T> data)
{
	infoprintf( "insertLinesInTable:...\n");
	try {
		std::string query =
			"insert into " + _current_tablename +  " values" ;
		for (int i = 0; i < data.size(); i++) {
			T datai = data[i];
			std::string value = "default,";//set default for Field 'id',which is setted as primary-key and auto-increasing
			for (int j = 0; j < datai._type.size(); j++) {
				switch (datai._type[j])
				{
				case TINYINT:
					value += std::to_string(*(char*)datai._value[j]);
					break;
				case SMALLINT:
					value += std::to_string(*(short*)datai._value[j]);
					break;
				case MEDIUMINT:
				case INT:
				case YEAR:
					value += std::to_string(*(int*)datai._value[j]);
					break;
				case BIGINT:
					value += std::to_string(*(long*)datai._value[j]);
					break;
				case FLOAT:
				case DOUBLE:
				case DECIMAL:
					value += std::to_string(*(double*)datai._value[j]);
					break;
				case DATE:
					value += (*(mydate*)datai._value[j]).to_string();
					break;
				case TIME:
					value += (*(mytime*)datai._value[j]).to_string();
					break;
				case TIME_MS:
					value += (*(mytime_ms*)datai._value[j]).to_string();
					break;
				case DATETIME:
				case TIMESTAMP:
					value += (*(mydatetime*)datai._value[j]).to_string();
					break;
				case DATETIME_MS:
					value += (*(mydatetime_ms*)datai._value[j]).to_string();
					break;
				case CHAR:
				case VARCHAR:
				case TINYBLOB:
				case TINYTEXT:
				case BLOB:
				case TEXT:
				case MEDIUMBLOB:
				case MEDIUMTEXT:
				case LONGBLOB:
				case LONGTEXT:
					value += "'" + *(std::string*)datai._value[j] + "'";//add "'" at start and end of string target
					break;
				default:
					break;
				}
				value += ",";
			}
			value.erase(value.end() - 1);//erase the last ','
			query += ("(" + value + "),");
		}
		query.erase(query.end() - 1);//erase the last ','
		int effectRows = stmt->executeUpdate(query.c_str());// executeUpdate适用于 UPDATE INSERT  DELETE
		infoprintf("插入数据：%d行\n", effectRows);

		_iscommitted = false;//数据库已修改，但尚未提交
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" insertLinesInTable failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" insertLinesInTable failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}

template<class T>
int MySqlAPI::updateTable(T datai)
{
	infoprintf( "updateTable:...\n");
	try
	{
		std::string query =	"update " + _current_tablename + " set";
		for (int j = 0; j < datai._type.size(); j++) {
			query += (" " + datai._name[j] + "=");
			std::string value;
			switch (datai._type[j])
			{
			case TINYINT:
				value = std::to_string(*(char*)datai._value[j]);
				break;
			case SMALLINT:
				value = std::to_string(*(short*)datai._value[j]);
				break;
			case MEDIUMINT:
			case INT:
			case YEAR:
				value = std::to_string(*(int*)datai._value[j]);
				break;
			case BIGINT:
				value = std::to_string(*(long*)datai._value[j]);
				break;
			case FLOAT:
			case DOUBLE:
			case DECIMAL:
				value = std::to_string(*(double*)datai._value[j]);
				break;
			case DATE:
				value = (*(mydate*)datai._value[j]).to_string();
				break;
			case TIME:
				value = (*(mytime*)datai._value[j]).to_string();
				break;
			case TIME_MS:
				value = (*(mytime_ms*)datai._value[j]).to_string();
				break;
			case DATETIME:
			case TIMESTAMP:
				value = (*(mydatetime*)datai._value[j]).to_string();
				break;
			case DATETIME_MS:
				value = (*(mydatetime_ms*)datai._value[j]).to_string();
				break;
			case CHAR:
			case VARCHAR:
			case TINYBLOB:
			case TINYTEXT:
			case BLOB:
			case TEXT:
			case MEDIUMBLOB:
			case MEDIUMTEXT:
			case LONGBLOB:
			case LONGTEXT:
				value = "'" + *(std::string*)datai._value[j] + "'";//add "'" at start and end of string target
				break;
			default:
				break;
			}
			query += (value + ",");
		}
		query.pop_back();//pop back the last ","
		query += " where id = " + std::to_string(datai.id);
		
		int effectRows = stmt->executeUpdate(query.c_str());// executeUpdate适用于 UPDATE INSERT  DELETE
		infoprintf("修改数据：%d行\n", effectRows);

		_iscommitted = false;//数据库已修改，但尚未提交
	}
	catch (sql::SQLException& ex)
	{
		errorprintf(" insertLinesInTable failed,sql::SQLException occured,error code: %d, error message: %s\n", ex.getErrorCode(), ex.what());
		return -1;
	}
	catch (std::exception& ex)
	{
		errorprintf(" insertLinesInTable failed,std::exception occured,error message: %s\n", ex.what());
		return -1;
	}
	return 0;
}